package com.tencent.qplus.paddata.stat2;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.HashMap;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.concurrent.atomic.AtomicInteger;

import com.tencent.qplus.paddata.db.DataSourceManager;
import com.tencent.qplus.paddata.stat.Job;
import com.tencent.qplus.paddata.util.JdbcUtils;

public class StatLoginTimesJob extends Job {
	
	private Connection conn;
	private Connection statResultConn;
	private DecimalFormat dfc = new DecimalFormat("0.00%");

	@Override
	public void run() throws Exception {
		 try {
				conn = DataSourceManager.getInstance().getDataSource("padversiondatadb").getConnection();
				statResultConn = DataSourceManager.getInstance().getDataSource("padversiondatastatdb").getConnection();
				String objStatDate = dateUtils.getDateStrByDaysFromDate(statDate, -7);
				
				String sql = "select uin,loginVersion from padversiondata.newuin where firstLoginDate='"+objStatDate+"'";
				String resultSql = "REPLACE INTO newuserlogindaysstat(day,totalNewUV,loginUV,loginDays,periodLength,loginVersion) VALUES(?,?,?,?,7,?);";
				
				Map<String,LoginTimes> newUinLoginTimesMap = new HashMap<String,LoginTimes>();
				Statement st = null;
				PreparedStatement resultPst = null;
				ResultSet rs = null;
				Statement countSt = null;
				int totalNewUv = 0;
				try {
					st = conn.createStatement();
					countSt = conn.createStatement();
					resultPst = statResultConn.prepareStatement(resultSql);
					
					rs = st.executeQuery(sql);
					while(rs.next()){
						totalNewUv ++;
						String version = rs.getString("loginVersion");
						String uin = rs.getString("uin");
						LoginTimes  lt = newUinLoginTimesMap.get(version);
						if(lt==null){
							lt = new LoginTimes();
							newUinLoginTimesMap.put(version, lt);
						}
						lt.newUinList.add(uin);
					}
				} catch (SQLException e) {
					logger.error(e);
					throw e;
				}finally{
					JdbcUtils.release(rs);
					JdbcUtils.release(st);
				}
				
				Set<String> keySet = newUinLoginTimesMap.keySet();
				LoginTimes allVersionLoginTimes = new LoginTimes();
				for(String version:keySet){
					
					LoginTimes lt = newUinLoginTimesMap.get(version);
					for(String uin:lt.newUinList){
						String countSql = "select count(1) from uinlogin_"+ (Long.valueOf(uin)%100)+" where " +
								"uin='"+uin+"' and logindate>'"+objStatDate + "' and logindate<='"+statDate+"'";
						rs = countSt.executeQuery(countSql);
						if(rs.next()){
							int count = rs.getInt(1);
							if(lt.loginTimesMap.get(count)==null){
								lt.loginTimesMap.put(count, new AtomicInteger(0));
							}
							lt.loginTimesMap.get(count).addAndGet(1);
							if(allVersionLoginTimes.loginTimesMap.get(count)==null){
								allVersionLoginTimes.loginTimesMap.put(count, new AtomicInteger(0));
							}
							allVersionLoginTimes.loginTimesMap.get(count).addAndGet(1);
						}
					}
					for(Entry<Integer, AtomicInteger> entry:lt.loginTimesMap.entrySet()){
						resultPst.setString(1, objStatDate);
						resultPst.setInt(2, lt.newUinList.size());
						resultPst.setInt(3, entry.getValue().get());
						resultPst.setInt(4, entry.getKey());
						resultPst.setString(5, version);
						resultPst.addBatch();
					}
					
				}
				for(Entry<Integer, AtomicInteger> entry:allVersionLoginTimes.loginTimesMap.entrySet()){
					resultPst.setString(1, objStatDate);
					resultPst.setInt(2, totalNewUv);
					resultPst.setInt(3, entry.getValue().get());
					resultPst.setInt(4, entry.getKey());
					resultPst.setString(5, "");
					resultPst.addBatch();
				}
				resultPst.executeBatch();
				statLoginDays();
			} catch (SQLException e) {
				logger.error(e);
				throw e;
			}finally{
				JdbcUtils.release(conn);
				JdbcUtils.release(statResultConn);
			}
		 

	}

	
	private void statLoginDays() throws SQLException{
		String objDate = dateUtils.getDateStrByDaysFromDate(statDate, -7);
		String sql = "SELECT totalNewUV,SUM(loginUV),loginVersion FROM newuserlogindaysstat WHERE `day`=? AND loginDays>=? GROUP BY loginVersion;";
		String[] daysLevel = {"2","5"};
		String insertSql = "insert INTO newuserlogindaysstat2(`day`,totalNewUV,loginDays,rate,loginVersion)VALUES(?,?,?,?,?);";
		PreparedStatement pst1 = null;
		PreparedStatement pst2 = null;		
		try {
			pst1 = statResultConn.prepareStatement(sql);
			pst2 = statResultConn.prepareStatement(insertSql);
			ResultSet rs = null;
			for(String day:daysLevel){
				pst1.setString(1, objDate);
				pst1.setString(2, day);
				rs = pst1.executeQuery();
				while(rs.next()){
					int totalNewUV = rs.getInt(1);
					int loginUV = rs.getInt(2);
					String version = rs.getString(3);
					
					pst2.setString(1, objDate);
					pst2.setInt(2, totalNewUV);
					pst2.setString(3, day);
					pst2.setString(4, dfc.format(loginUV/(float)totalNewUV));
					pst2.setString(5, version);
					pst2.addBatch();
				}
			}
			pst1.execute("delete from newuserlogindaysstat2 where `day`='"+objDate+"'");
			pst2.executeBatch();
		} catch (SQLException e) {
			logger.error(e);
			throw e;
		}finally{
			JdbcUtils.release(pst1);
			JdbcUtils.release(pst2);
			
		}
	}
	

	public void mutildays() throws SQLException{
		try {
			statResultConn = DataSourceManager.getInstance().getDataSource("padversiondatastatdb").getConnection();
			for(int i=2;i<40;i++){
				System.out.println(this.statDate);
				this.statDate = dateUtils.getDateStrByDays(-i);
			}
		} catch (SQLException e) {
			logger.error(e);
			throw e;
		}
	}
	
	@Override
	public void init() {
		// TODO Auto-generated method stub

	}
	
	public static void main(String[] args){
		StatLoginTimesJob job = new StatLoginTimesJob();
		job.stat(args);
		//job.mutildays();
	}

}
